package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.Absent;
import com.hlw.hr_man.dao.AbsentDao;
import com.hlw.hr_man.util.JDBCUtil;

public class AbsentDaoImpl implements AbsentDao {

	@Override
	public List<Absent> selectEmployeeByEid(int eid, int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_absent.absent_starttime,");
		sql.append("hr_absent.absent_endtime from hr_absent,hr_employee where ");
		sql.append("hr_absent.e_id=hr_employee.e_id and hr_employee.e_id=? limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<Absent> absents = new ArrayList<Absent>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			pstmt.setInt(2, start);
			pstmt.setInt(3, pageSize);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Absent a = new Absent();
				a.setE_number(rs.getString("e_number"));
				a.setE_name(rs.getString("e_name"));
				a.setAbsent_starttime(rs.getString("absent_starttime"));
				a.setAbsent_endtime(rs.getString("absent_endtime"));
				absents.add(a);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return absents;
	}

	@Override
	public int selectEmployeeAbsent(int eid) {
		StringBuffer sql = new StringBuffer("select count(absent_id) absent_count from hr_absent where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int absent_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				absent_count = rs.getInt("absent_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return absent_count;
	}

	@Override
	public List<Absent> selectEmployeeAbsentBySearch(String searchType, String searchContent, int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_absent.absent_starttime,");
		sql.append("hr_absent.absent_endtime from hr_absent,hr_employee where ");
		sql.append("hr_absent.e_id=hr_employee.e_id");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		sql.append(" order by hr_employee.e_number");
		sql.append(" limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<Absent> absents = new ArrayList<Absent>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (searchType.equals("查找所有")) {
				pstmt.setInt(1, start);
				pstmt.setInt(2, pageSize);
			} else {
				pstmt.setString(1, "%"+searchContent+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, pageSize);
			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Absent a = new Absent();
				a.setE_number(rs.getString("e_number"));
				a.setE_name(rs.getString("e_name"));
				a.setAbsent_starttime(rs.getString("absent_starttime"));
				a.setAbsent_endtime(rs.getString("absent_endtime"));
				absents.add(a);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return absents;
	}

	@Override
	public int selectEmployeeAbsentCount(String searchType, String searchContent) {
		StringBuffer sql = new StringBuffer("select count(absent_id) absent_count from hr_absent,hr_employee where hr_absent.e_id=hr_employee.e_id");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		Connection conn = JDBCUtil.getConnection();
		int absent_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (!searchType.equals("查找所有")){
				pstmt.setString(1, "%"+searchContent+"%");
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				absent_count = rs.getInt("absent_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return absent_count;
	}

	@Override
	public int insertAbsent(Absent absent) {
		StringBuffer sql = new StringBuffer("insert into hr_absent values(?,?,?,?)");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, absent.getAbsent_id());
			pstmt.setInt(2, absent.getE_id());
			pstmt.setString(3, absent.getAbsent_starttime());
			pstmt.setString(4, absent.getAbsent_endtime());
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int deleteAbsentByEid(int e_id) {
		StringBuffer sql = new StringBuffer("delete from hr_absent where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, e_id);
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}
}
